using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Data.Common;
using System.CodeDom;
using System.CodeDom.Compiler;
using System.IO;
using Microsoft.CSharp;

using RikMigrations.Providers;

namespace RikMigrations
{
    public class MigrationGenerator
    {
		static CodeCompileUnit GenerateEmptyMigration(string name, int version, out CodeMemberMethod upMethod, out CodeMemberMethod downMethod)
		{
			CodeCompileUnit ccu = new CodeCompileUnit();
			ccu.AssemblyCustomAttributes.Add(new CodeAttributeDeclaration("RikMigrations.Migration", new CodeAttributeArgument(new CodeTypeOfExpression("Migrations." + name)), new CodeAttributeArgument(new CodePrimitiveExpression(version))));

			CodeNamespace n = new CodeNamespace("Migrations");
			ccu.Namespaces.Add(n);
			CodeTypeDeclaration t = new CodeTypeDeclaration(name);
			t.BaseTypes.Add(new CodeTypeReference(typeof(IMigration)));
			n.Types.Add(t);

			upMethod = new CodeMemberMethod();
			upMethod.Name = "Up";
			upMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
			upMethod.ImplementationTypes.Add(new CodeTypeReference(typeof(IMigration)));
			upMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(DbProvider), "db"));
			t.Members.Add(upMethod);

			downMethod = new CodeMemberMethod();
			downMethod.Name = "Down";
			downMethod.Attributes = MemberAttributes.Public | MemberAttributes.Final;
			downMethod.ImplementationTypes.Add(new CodeTypeReference(typeof(IMigration)));
			downMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(DbProvider), "db"));
			t.Members.Add(downMethod);

			return ccu;
		}

		static bool NeedsLength(SqlColumnDescriptor col)
		{
			if (col.Length == null)
				return false;
			switch (col.Type.ToLower())
			{
				case "ntext":
				case "image":
					return false;
				default:
					return true;
			}
		}

		public static void GenerateMigration(DbProvider db, string filename, bool separateFiles)
		{
			CodeDomProvider provider = CodeDomProvider.CreateProvider("CSharp");
		    
			Log.WriteLine("Loading database schema");
			List<SqlTableDescriptor> tables = MigrationGenerator.GetTables(new MssqlProvider());

			CodeCompileUnit ccu = null;
			CodeMemberMethod upMethod = null, downMethod = null;
			int migrationCount = 1;

			foreach (MigrationGenerator.SqlTableDescriptor table in GetOrderedTables(tables))
			{
				if (ccu == null || separateFiles)
				{
					string tableName = separateFiles ? table.Name + "Migration" : "Migration";
					ccu = GenerateEmptyMigration(tableName, migrationCount++, out upMethod, out downMethod);
					upMethod.Statements.Add(new CodeVariableDeclarationStatement(typeof(Table), "table"));
				}
				upMethod.Statements.Add(new CodeCommentStatement("TABLE: " + table.Name));

				upMethod.Statements.Add(new CodeAssignStatement(
					new CodeVariableReferenceExpression("table"),
					new CodeMethodInvokeExpression(new CodeArgumentReferenceExpression("db"), "AddTable", new CodePrimitiveExpression(table.Name))));

				foreach (MigrationGenerator.SqlColumnDescriptor col in table.Columns)
				{
					CodeMethodInvokeExpression e;
					if (NeedsLength(col))
						e = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("table"), "AddColumn", new CodePrimitiveExpression(col.Name), new CodeTypeOfExpression(col.GetClrType()), new CodePrimitiveExpression(col.Length));
					else
						e = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("table"), "AddColumn", new CodePrimitiveExpression(col.Name), new CodeTypeOfExpression(col.GetClrType()));
					if (col.IsPrimaryKey)
						e = new CodeMethodInvokeExpression(e, "PrimaryKey");
					if (!col.IsNullable)
						e = new CodeMethodInvokeExpression(e, "NotNull");
					if (col.DefaultValue != null)
						e = new CodeMethodInvokeExpression(e, "Default", new CodePrimitiveExpression(col.DefaultValue));
					if (col.IsUnique)
						e = new CodeMethodInvokeExpression(e, "Unique");
					// TODO: Support multiple foreign keys?
					if (col.ForeignKey != null)
						e = new CodeMethodInvokeExpression(e, "References", new CodePrimitiveExpression(col.ForeignKey.Table), new CodePrimitiveExpression(col.ForeignKey.Column));
					if (col.IsBlob)
						e = new CodeMethodInvokeExpression(e, "Blob");
					if (col.IsString && !col.IsUnicode)
						e = new CodeMethodInvokeExpression(e, "NotUnicode");
					if (col.IsAutoGenerated)
					{
						if (col.IdentityIncrement == 1 && col.IdentitySeed == 1)
							e = new CodeMethodInvokeExpression(e, "AutoGenerate");
						else
							e = new CodeMethodInvokeExpression(e, "AutoGenerate", new CodePrimitiveExpression(col.IdentitySeed), new CodePrimitiveExpression(col.IdentityIncrement));
					}
					upMethod.Statements.Add(e);
				}

				foreach (Constraint con in table.Constraints)
				{
					List<CodeExpression> parameters = new List<CodeExpression>();
					parameters.Add(new CodePrimitiveExpression(con.Name));
					parameters.Add(new CodeFieldReferenceExpression(new CodeTypeReferenceExpression(typeof(ConstraintType)), con.Type.ToString()));
					foreach (string column in con.Columns)
						parameters.Add(new CodePrimitiveExpression(column));
					upMethod.Statements.Add(new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("table"), "AddConstraint", parameters.ToArray()));
				}

				upMethod.Statements.Add(new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("table"), "Save"));

				downMethod.Statements.Insert(0, new CodeExpressionStatement(new CodeMethodInvokeExpression(new CodeArgumentReferenceExpression("db"), "DropTable", new CodePrimitiveExpression(table.Name))));
				if (separateFiles)
				{
					using (TextWriter writer = new StreamWriter(string.Format(filename, table.Name)))
						provider.GenerateCodeFromCompileUnit(ccu, writer, new CodeGeneratorOptions());
				}
				else
					upMethod.Statements.Add(new CodeSnippetStatement());
			}

            foreach (SqlViewDescriptor view in GetViews(new MssqlProvider()))
            {
                if (separateFiles)
                {
                    string viewName = separateFiles ? view.Name + "Migration" : "Migration";
                    ccu = GenerateEmptyMigration(viewName, migrationCount++, out upMethod, out downMethod);
                }
                if (!upMethod.Statements.Contains(new CodeVariableDeclarationStatement(typeof (View), "view")))
                {
                    upMethod.Statements.Add(new CodeVariableDeclarationStatement(typeof (View), "view"));
                }
                
                upMethod.Statements.Add(new CodeCommentStatement("VIEW: " + view.Name));
                
                upMethod.Statements.Add(new CodeAssignStatement(
                    new CodeVariableReferenceExpression("view"),
                    new CodeMethodInvokeExpression(new CodeArgumentReferenceExpression("db"), "AddView", new CodePrimitiveExpression(view.Name),new CodePrimitiveExpression(view.Script))));

                upMethod.Statements.Add(new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("view"), "Save"));

                downMethod.Statements.Insert(0,
                                             new CodeExpressionStatement(new CodeMethodInvokeExpression(
                                                                             new CodeArgumentReferenceExpression("db"),
                                                                             "DropView",
                                                                             new CodePrimitiveExpression(view.Name))));

                if (separateFiles)
                {
                    using (TextWriter writer = new StreamWriter(string.Format(filename, view.Name)))
                        provider.GenerateCodeFromCompileUnit(ccu, writer, new CodeGeneratorOptions());
                }
                else
                    upMethod.Statements.Add(new CodeSnippetStatement());
            }
            

			if (!separateFiles)
				using (TextWriter writer = new StreamWriter(filename))
					provider.GenerateCodeFromCompileUnit(ccu, writer, new CodeGeneratorOptions());
            // TODO: Check code to give app the ability to Compile at runtime the cs generated
//		    String[] ReferenceAssemblies = new string[] {"System.Dll", "RikMigrations.exe"};
//		    string assemblyname = "MigrationGenerated.dll";
//		    CompilerParameters compParameters = new CompilerParameters(ReferenceAssemblies, assemblyname);
//            compParameters.GenerateExecutable = false;
//            compParameters.GenerateInMemory = false;
//
//		    provider.CompileAssemblyFromDom(compParameters, ccu);

		}

		private static IEnumerable<SqlTableDescriptor> GetOrderedTables(List<SqlTableDescriptor> tables)
		{
			DependencyTree<SqlTableDescriptor> tree = new DependencyTree<SqlTableDescriptor>();
			foreach (SqlTableDescriptor table in tables)
			{
				List<SqlTableDescriptor> dependancies = tables.FindAll(delegate(SqlTableDescriptor a) { return table.DependsOnTables.Contains(a.Name); });
				tree.Add(table, dependancies);
			}

			// Traverse tree
			List<SqlTableDescriptor> added = new List<SqlTableDescriptor>();
			foreach (SqlTableDescriptor table in tree.VisitAllNodes())
			{
				if (!added.Contains(table))
				{
					List<SqlTableDescriptor> dependancies = tables.FindAll(delegate(SqlTableDescriptor a) { return table.DependsOnTables.Contains(a.Name); });
					// if (all dependencies already added)
					if (dependancies.TrueForAll(delegate(SqlTableDescriptor a) { return added.Contains(a); }))
					{
						added.Add(table);
						yield return table;
					}
				}
				if (added.Count == tables.Count)
					break;
			}
		}

        public static List<SqlViewDescriptor> GetViews(MssqlProvider db)
        {
            List<SqlViewDescriptor> retVal = new List<SqlViewDescriptor>();

            db.CreateTransaction();
            try
            {
                using (DbDataReader reader = db.ExecuteReader("select * from information_schema.views where table_name not in ('syssegments', 'sysconstraints')"))
                    while (reader.Read())
                        retVal.Add(new SqlViewDescriptor(reader));
                foreach (SqlViewDescriptor view in retVal)
                {
                    Log.WriteLine("Loading schema details for " + view.Name);
                    view.LoadScript(db);
                }
            }
            finally {db.CommitTransaction();}
            return retVal;
        }

		public static List<SqlTableDescriptor> GetTables(MssqlProvider db)
		{
			List<SqlTableDescriptor> retVal = new List<SqlTableDescriptor>();

			db.CreateTransaction();
			try
			{
				using (DbDataReader reader = db.ExecuteReader("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"))
					while (reader.Read())
						retVal.Add(new SqlTableDescriptor(reader));
				foreach (SqlTableDescriptor table in retVal)
				{
					Log.WriteLine("Loading schema details for " + table.Name);
					table.LoadColumnsAndConstraints(db);
				}
			}
			finally { db.CommitTransaction(); }
			return retVal;
		}

		// TODO: Remove and replace with Table class in generator
		public class SqlTableDescriptor
		{
			Dictionary<string, SqlColumnDescriptor> columns = new Dictionary<string, SqlColumnDescriptor>();
			Dictionary<string, Constraint> constraints = new Dictionary<string, Constraint>();

			public SqlTableDescriptor(DbDataReader reader)
			{
				int i = reader.GetOrdinal("TABLE_NAME");
				name = reader.GetString(i);
			}

			internal void LoadColumnsAndConstraints(MssqlProvider db)
			{
				using (DbDataReader reader = db.ExecuteReader("SELECT *, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IsIdentity, IDENT_SEED(TABLE_NAME) AS IdentitySeed, IDENT_INCR(TABLE_NAME) AS IdentityIncrement FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0 ORDER BY ORDINAL_POSITION", name))
					while (reader.Read())
					{
						SqlColumnDescriptor col = new SqlColumnDescriptor(reader, this);
						columns.Add(col.Name, col);
						orderedColumns.Add(col);
					}
				using (DbDataReader reader = db.ExecuteReader(@"SELECT COLUMN_NAME, CONSTRAINT_TYPE, a.CONSTRAINT_NAME,
	(SELECT COUNT(*) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c WHERE a.CONSTRAINT_NAME = c.CONSTRAINT_NAME) AS ColumnCount
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
WHERE a.TABLE_NAME = @0", Name))
					while (reader.Read())
					{
						string column = reader.GetString(0);
						string constraintType = reader.GetString(1);
						string constraintName = reader.GetString(2);
						int columnCount = reader.GetInt32(3);
						if (columnCount == 1)
						{
							SqlColumnDescriptor col = columns[column];
							switch (constraintType)
							{
								case "UNIQUE":
									col.IsUnique = true;
									break;
								case "PRIMARY KEY":
									col.IsPrimaryKey = true;
									break;
							}
						}
						else
						{
							Constraint constraint;
							if (!constraints.TryGetValue(constraintName, out constraint))
							{
								ConstraintType? type = null;
								switch (constraintType)
								{
									case "UNIQUE":
										type = ConstraintType.Unique;
										break;
									case "PRIMARY KEY":
										type = ConstraintType.PrimaryKey;
										break;
								}
								if (type != null)
								{
									constraint = new Constraint(constraintName, type.Value, null);
									constraints.Add(constraintName, constraint);
								}
							}
							constraint.Columns.Add(column);
						}
					}
				using (DbDataReader reader = db.ExecuteReader(@"SELECT
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME
WHERE FK.TABLE_NAME = @0", Name))
				{
					while (reader.Read())
					{
						string column = reader.GetString(0);
						SqlColumnDescriptor col = columns[column];
						col.ForeignKey = new ColumnReference(reader.GetString(1), reader.GetString(2));
						if (!dependsOnTables.Contains(reader.GetString(1)) && reader.GetString(1) != Name)
							dependsOnTables.Add(reader.GetString(1));
					}
				}
			}

			#region Properties
			private string name;
			public string Name
			{
				get { return name; }
			}

			private List<SqlColumnDescriptor> orderedColumns = new List<SqlColumnDescriptor>();
			public List<SqlColumnDescriptor> Columns
			{
				get { return orderedColumns; }
			}

			public ICollection<Constraint> Constraints
			{
				get { return constraints.Values; }
			}

			private List<string> dependsOnTables = new List<string>();
			/// <summary>
			/// Lists which tables are referenced by foreign keys.
			/// </summary>
			public List<string> DependsOnTables
			{
				get { return dependsOnTables; }
			}
			#endregion
		}

        public class SqlViewDescriptor
        {
            private string script;
            public string Script
            {
                get { return script; }
            }
            public SqlViewDescriptor(DbDataReader reader)
            {
                int i = reader.GetOrdinal("TABLE_NAME");
                name = reader.GetString(i);
            }

            internal void LoadScript(MssqlProvider db)
            {
                object o =
                    db.ExecuteScalar(
                        String.Format("select view_Definition from information_schema.views where table_name = '{0}'",
                                      name));
                int index = ((string) o).IndexOf("Select",StringComparison.InvariantCultureIgnoreCase);
                script = ((String) o).Substring(index);
            }

            #region Properties
            private string name;
            public string Name
            {
                get { return name; }
            }
            #endregion
        }

		// TODO: Remove and replace with Column class in generator
		public class SqlColumnDescriptor
		{
			SqlTableDescriptor table;

			public SqlColumnDescriptor(DbDataReader reader, SqlTableDescriptor table)
			{
				this.table = table;
				int i = reader.GetOrdinal("COLUMN_NAME");
				name = reader.GetString(i);
				i = reader.GetOrdinal("DATA_TYPE");
				type = reader.GetString(i);
				i = reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH");
				if (!reader.IsDBNull(i))
					length = reader.GetInt32(i);
				i = reader.GetOrdinal("COLUMN_DEFAULT");
				if (!reader.IsDBNull(i))
					defaultValue = reader.GetString(i);
				isNullable = reader.GetString(reader.GetOrdinal("IS_NULLABLE")) == "YES";
				isAutoGenerated = reader.GetInt32(reader.GetOrdinal("IsIdentity")) == 1;
				if (isAutoGenerated)
				{
					identitySeed = Convert.ToInt32(reader.GetValue(reader.GetOrdinal("IdentitySeed")));
					identityIncrement = Convert.ToInt32(reader.GetValue(reader.GetOrdinal("IdentityIncrement")));
				}
			}

			#region Properties
			private string name;
			public string Name
			{
				get { return name; }
			}

			private string type;
			public string Type
			{
				get { return type; }
			}

			private int? length;
			public int? Length
			{
				get { return length; }
			}

			private bool isNullable;
			public bool IsNullable
			{
				get { return isNullable; }
			}

			private string defaultValue;
			public string DefaultValue
			{
				get { return defaultValue; }
			}

			private bool isPrimaryKey;
			public bool IsPrimaryKey
			{
				get { return isPrimaryKey; }
				set { isPrimaryKey = value; }
			}

			private bool isUnique;
			public bool IsUnique
			{
				get { return isUnique; }
				set { isUnique = value; }
			}

			private ColumnReference foreignKey;
			public ColumnReference ForeignKey
			{
				get { return foreignKey; }
				set { foreignKey = value; }
			}

			public bool IsBlob
			{
				get
				{
					switch (Type)
					{
						case "text":
						case "ntext":
						case "image":
							return true;
						default:
							return false;
					}
				}
			}

			public bool IsString
			{
				get
				{
					switch (Type)
					{
						case "char":
						case "nchar":
						case "varchar":
						case "nvarchar":
						case "text":
						case "ntext":
							return true;
						default:
							return false;
					}
				}
			}

			public bool IsUnicode
			{
				get
				{
					switch (Type)
					{
						case "nchar":
						case "nvarchar":
						case "ntext":
							return true;
						default:
							return false;
					}
				}
			}

			bool isAutoGenerated;
			public bool IsAutoGenerated
			{
				get { return isAutoGenerated; }
			}

			private int identitySeed;
			public int IdentitySeed
			{
				get { return identitySeed; }
			}

			private int identityIncrement;
			public int IdentityIncrement
			{
				get { return identityIncrement; }
			}
			#endregion

			public Type GetClrType()
			{
				return MssqlProvider.GetClrType(Type);
			}
		}
	}
}